﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using fuzhu;
using System.Data.SqlClient;
using System.Net;
using System.IO;

namespace GLY_XieTong
{

    public class TongBu
    {
        
        string cConStr { get;set; }
        // A,B 账连接字符串,
        /// <summary>
        /// 来源链接字符串
        /// </summary>
        public string cConStrA { get; set; }
        /// <summary>
        /// 目标字符串
        /// </summary>
        public string cConStrB { get; set; }

        public string cConStrZD { get; set; }
        public string cError { get; set; }
        public int iDBID { get; set; }
        public string cZJDATA { get; set; }  //中间库名
        public TongBu(string conStr,int DBID)
         {
           
            cConStr = conStr;
            cZJDATA = GetInterDataBase();

            string cDataA = GetDataA();
            cConStrA = cConStr.Replace(cZJDATA, cDataA);           

            string cDataB = GetDataB();
            cConStrB = cConStr.Replace(cZJDATA, cDataB);
          
        }



        #region  插入基础档案
        /// <summary>
        /// 基础档案,不考虑时间因素，考虑双主键和过滤条件
        /// </summary>
        public string INSDangAn(string cLx)
        {
            try
            {
                //CheckTimeTable();
                cError = "";
                //查询同步的数据表及主键
                string sql = string.Format(@" SELECT  [id] ,[clx] ,[ctable] ,[PK],PK2,CZJBFILTER,cdatecolumn,cdelfilter,ctimestamp
  FROM [zdy_inter_table] WHERE  CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cTarTable =  DbHelper.GetDbString(dt1.Rows[0]["ctable"]);              
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    //第二主键
                    string cPK2 = DbHelper.GetDbString(dt1.Rows[0]["PK2"]);
                    string cFilter = DbHelper.GetDbString(dt1.Rows[0]["CZJBFILTER"]);
                    //插入的字段和值
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cInsertColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //查询的字段，如果时间戳不为空，增加时间戳
                    string cSelectColumn = cInsertColumn;

                    string cTimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        cSelectColumn += string.Format(" ,convert(bigint,{0}) as {0}", cTimestamp);
                      
                    }
                    //删除过滤器
                    string cDelFilter = DbHelper.GetDbString(dt1.Rows[0]["cdelfilter"]);                   
                    string cRelation = "";
                    //更新语句用到的where条件
                    string[] cPKArray = cPK.Split('|');
                    foreach (string i in cPKArray)
                    { 
                        //权限是5个主键，程序里直接定义
                        cRelation += string.Format(" and  Tmp.{0}=T.{0}  ", i);
                    }

                    #region 权限更改成动态，使用|区分，写在一个字段里
                    //if (cLx == "权限")
                    //{
                    //    //权限是5个主键，程序里直接定义
                    //    cRelation = string.Format(" and  Tmp.cAcc_Id=T.cAcc_Id and  Tmp.iYear=T.iYear and  Tmp.cUser_Id=T.cUser_Id and  Tmp.cAuth_Id=T.cAuth_Id and  Tmp.iIsUser=T.iIsUser");
                    //}
                    //else if (cLx == "字段数据权限")
                    //{
                    //    //权限是3个主键，程序里直接定义
                    //    cRelation = string.Format(" and  Tmp.cUserId=T.cUserId and  Tmp.cKey=T.cKey and  Tmp.cFld=T.cFld  ");
                    //}
                    //else if (cLx == "业务对象表")
                    //{
                    //    //权限是3个主键，程序里直接定义
                    //    cRelation = string.Format(" and  Tmp.cBusObId=T.cBusObId and  Tmp.iAuthType=T.iAuthType and  Tmp.langid=T.langid  ");
                    //}
                    //else if (cLx == "字段权限分配")
                    //{
                    //    //权限是3个主键，程序里直接定义
                    //    cRelation = string.Format(" and  Tmp.cUserId=T.cUserId and  Tmp.cBusObId=T.cBusObId and  Tmp.cACCode=T.cACCode  ");
                    //}
                    //else if (cLx == "客户管理维度权限")
                    //{
                    //    //权限是3个主键，程序里直接定义
                    //    cRelation = string.Format(" and  Tmp.Account_ID=T.Account_ID and  Tmp.Privilege_Type=T.Privilege_Type and  Tmp.Privilege_ID=T.Privilege_ID  ");
                    //}
                    //else if (!string.IsNullOrEmpty(cPK2))
                    //{
                    //    cRelation = string.Format(" and  Tmp.{0}=T.{0} and  Tmp.{1}=T.{1}", cPK, cPK2);
                    //}
                    //else
                    //{
                    //    cRelation = string.Format(" and  Tmp.{0}=T.{0}  ", cPK);
                    //}
                    #endregion
                    //时间字段 及过滤条件, 多时间段也使用 | 进行分割
                    string cDateCol  = DbHelper.GetDbString(dt1.Rows[0]["cdatecolumn"]);
                 
                    if (!string.IsNullOrEmpty(cDateCol))
                    {
                        sql = string.Format("SELECT DUPDATETIME,DBFILTER FROM ZDY_INTER_XIETONG WHERE dbid ={0} AND clx = '{1}'  ", iDBID, cLx);
                        DateTime dDate = DbHelper.GetDbDate(DbHelper2.ExecuteScalar(sql, cConStr));
                         string[] cDateArray = cDateCol.Split('|');
                        foreach (string i in cDateArray)
                        {
                            //权限是5个主键，程序里直接定义
                            cFilter += string.Format(" and {0}>='{1}'", cDateCol, dDate);
                        }
                        //对账套的过滤设置
                        string cDBFilter  = DbHelper.GetDbString(dt1.Rows[0]["DBFILTER"]);
                        if (!string.IsNullOrEmpty(cDBFilter))
                        {
                            cFilter += cDBFilter;
                        }
                    }
                    
                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ',T.'+ccolumn+'=Tmp.'+ccolumn from zdy_inter_tables WHERE clx = '{0}'
 and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    
                    //创建目标库临时表
                    var tempTableName = @"#Temp" + cTarTable;
                    var createtempsql = string.Format("select {0} into {1} from {2} where 1=2", cSelectColumn, tempTableName, cTarTable);


                    var insertsql = string.Format(@"insert into {0}({1}) select {1} from  {2} Tmp  " +
                        "where not exists(select 1 from {0} T where 1=1 {3})  ;", cTarTable , cInsertColumn, tempTableName, cRelation);

                   
                    var updatesql = string.Format("UPDATE T SET {0} FROM {1} T INNER JOIN {2} Tmp ON 1=1 {3} ", cUpdateColunm, cTarTable, tempTableName, cRelation);

                    var deletesql = "";
                    if (cLx == "操作员")
                    {
                        deletesql = string.Format(" delete From a from {0} T,ua_log a   where T.cuser_id  =a.cuser_id and  not exists(select 1 from {1} Tmp where  1=1 {2}) {3}  ", cTarTable, tempTableName, cRelation, cDelFilter);
                    }

                    deletesql += string.Format(" delete From T from {0} T  where not exists(select 1 from {1} Tmp where  1=1 {2}) {3} ", cTarTable,tempTableName, cRelation,cDelFilter);

                    var dropsql = string.Format("DROP TABLE {0};", tempTableName);
                    //时间戳 ,如果存在，1影响更新。2保存到时间戳表.
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        updatesql += string.Format("  where   not exists(select 1 from zdy_zd_stamp S where clx='{0}' and  S.ctimestamp=CONVERT(bigint,Tmp.{1}))", cLx, cTimestamp);

                    }
                   

                    //获取源数据库的内容
                    var selectsql = string.Format("select {0} from {1} where 1=1 {2}", cSelectColumn, cTarTable,cFilter);
                    DataTable dt = DbHelper2.ExecuteTable(selectsql, cConStrB);

                    using (SqlConnection conn = new SqlConnection(cConStrA))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                command.CommandText = createtempsql;
                                command.ExecuteNonQuery();
                                //插入源表数据
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableName;
                                    bulkcopy.WriteToServer(dt);
                                    bulkcopy.Close();
                                }
                                //执行更新,有更新栏目的才进行更新
                                command.CommandTimeout = 300;
                                int updateQuery = 0;
                                if (!string.IsNullOrEmpty(cUpdateColunm))
                                {
                                    command.CommandText = updatesql;
                                    updateQuery = command.ExecuteNonQuery();
                                }
                               
                                //执行新增
                                command.CommandText = insertsql;
                                int insertQuery =command.ExecuteNonQuery();
                                //执行删除
                                command.CommandText = deletesql;
                                int delQuery = command.ExecuteNonQuery();
                                //时间戳表的更新

                               
                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    string cStampRelation = "";
                                    //不足5位主键的补足5位，后面的补空‘’
                                    string[] newPKAarray = new string[5];
                                    for (int i = 0; i <5; i++)
                                    {
                                        if (i< cPKArray.Length)
                                        {
                                            newPKAarray[i] = cPKArray[i];
                                            if (i == 0)
                                            {
                                                cStampRelation += string.Format(" and T.cpk=Tmp.{0}", cPKArray[i]);
                                            }
                                            else
                                            {
                                                cStampRelation += string.Format(" and T.cpk{0}=Tmp.{1}",i+1, cPKArray[i]);
                                            }
                                        }
                                        else
                                        {
                                            newPKAarray[i] = "0";
                                        }
                                    }

                                    //设置新的关联关系
                                    //主键没有的插入
                                    command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,cpk2,cpk3,cpk4,cpk5,ctimestamp)
                                    select '{0}',{1},{2},{3},{4},{5},{6}  from {7} Tmp where not exists (select 1 from zdy_zd_stamp T where 1=1 {8} and clx='{0}') ",
                                        cLx, newPKAarray[0], newPKAarray[1], newPKAarray[2], newPKAarray[3], newPKAarray[4], cTimestamp, tempTableName, cStampRelation);
                                        command.ExecuteNonQuery();
                                        //相同的更新
                                        command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0}
 from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' {3} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableName, cLx, cStampRelation);
                                        command.ExecuteNonQuery();

                                    #region  根据主键插入，作废，更改成上面动态的
                                    //                                   {
                                    //                                       //主键没有的插入
                                    //                                       command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,cpk2,ctimestamp)
                                    //                                   select '{0}',{1},{4},{2}  from {3} Tmp where not exists (select 1 from zdy_zd_stamp T where t.cpk=Tmp.{1} and clx='{0}') ",
                                    //                                       cLx, cPK, cTimestamp, tempTableName, cPK2);
                                    //                                       command.ExecuteNonQuery();
                                    //                                       //相同的更新
                                    //                                       command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0},ddate= getdate()
                                    //from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' and T.cpk=Tmp.{3} and t.cpk2=Tmp.{4} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableName, cLx, cPK, cPK2);
                                    //                                       command.ExecuteNonQuery();

                                    //
                                    #endregion}
                                }
                                cError = string.Format("更新{0}条数据，新增{1}条数据，删除{2}条数据", updateQuery, insertQuery, delQuery);
                            }
                            catch (Exception ex)
                            {
                               cError = string.Format("error:{0}",  ex.Message );
                                
                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }
                }
                else
                {
                    cError= "error:没有配置此档案同步";
                }

                SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@ctarcode",iDBID),
                                         new SqlParameter("@CLX",cLx),
                                           new SqlParameter("@CREMARK",cError)

          };

                sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(@ctarcode, getdate(),@CREMARK,@CLX)");
                DbHelper2.ExecuteNonQuery(sql,param,cConStr);

                if (cError.StartsWith("error"))
                {
                    return cError;
                }
                else
                {
                    return "ok:"+ cError;
                }
            }
            catch (Exception ex)
            {
              string   sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values({2}, getdate(),'{0}','{1}')", ex.Message, cLx,iDBID);
                DbHelper2.ExecuteNonQuery(sql, cConStr);
                return "error:" + ex.Message;
                //WriteLog(cLx+"档案同步："+ex.Message);
            }
        }
        #endregion


        /// <summary>
        /// 中间库
        /// </summary>
        /// <returns></returns>
        public string GetInterDataBase()
        {
            string sql = @"select DB_NAME()";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
        }
        /// <summary>
        /// 获取A的连接字符串
        /// </summary>
        /// <returns></returns>
        public string GetConStrA()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='7'";
            string cDataA = DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));
            return cConStr.Replace(cZJDATA, cDataA);
        }
        /// <summary>
        /// 获取A的连接字符串
        /// </summary>
        /// <returns></returns>
        public string GetDataA()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='7'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }

        public string GetDataB()
        {
            string SQL = "SELECT CVALUE  FROM ZDY_INTER_PARA WHERE LX='8'";
            return DbHelper.GetDbString(DbHelper2.ExecuteScalar(SQL, cConStr));

        }
    }
}
